package com.cheney.demo.data;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.*;

/**
 * @author si.chen
 * @date 2024/12/22 16:45
 */
//用Java，实现如图的转换，输入文件来自D:/tmp/import3.xlsx，输出文件保存在D:/tmp/export3.xlsx，输入的空字符串也要识别
public class K去重_03 {
    public static void main(String[] args) throws IOException {
        // 读取输入Excel文件
        List<String> inputData = readExcelFile("D:/tmp/import3.xlsx");
        
        // 统计每个值的出现次数
        Map<String, Integer> countMap = new HashMap<>();
        for (String value : inputData) {
            countMap.merge(value, 1, Integer::sum);
        }
        
        // 将统计结果转换为列表并排序
        List<Map.Entry<String, Integer>> sortedEntries = new ArrayList<>(countMap.entrySet());
        sortedEntries.sort((e1, e2) -> {
            // 首先按照出现次数降序排序
            int compareByCount = e2.getValue().compareTo(e1.getValue());
            // 如果次数相同，则按照值的字典序升序排序
            return compareByCount != 0 ? compareByCount : e1.getKey().compareTo(e2.getKey());
        });
        
        // 创建新的工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Result");
        
        // 写入标题行
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("a");
        headerRow.createCell(1).setCellValue("重复数");
        
        // 写入数据
        int rowIndex = 1;
        for (Map.Entry<String, Integer> entry : sortedEntries) {
            Row row = sheet.createRow(rowIndex++);
            row.createCell(0).setCellValue(entry.getKey());
            row.createCell(1).setCellValue(entry.getValue());
        }
        
        // 自动调整列宽
        for (int i = 0; i < 2; i++) {
            sheet.autoSizeColumn(i);
        }
        
        // 保存文件
        try (FileOutputStream fileOut = new FileOutputStream("D:/tmp/export3.xlsx")) {
            workbook.write(fileOut);
        }
        workbook.close();
    }
    
    private static List<String> readExcelFile(String filePath) throws IOException {
        List<String> data = new ArrayList<>();
        try (FileInputStream inputStream = new FileInputStream(new File(filePath));
             Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            // 跳过标题行
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    Cell cell = row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    if (cell.getCellType() == CellType.BLANK) {
                        data.add("");
                    } else if (cell.getCellType() == CellType.NUMERIC) {
                        data.add(String.format("%.0f", cell.getNumericCellValue()));
                    } else {
                        data.add(cell.toString().trim());
                    }
                }
            }
        }
        return data;
    }
}
